package cn.smbms.dao.provider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.smbms.dao.BaseDao;
import cn.smbms.pojo.Page;
import cn.smbms.pojo.Provider;
import cn.smbms.pojo.User;

public class ProviderDaoImpl implements ProviderDao{

	@Override
	public boolean add(Connection connection, Provider provider)
			throws Exception {
		PreparedStatement pstm=null;
		boolean flag=false;
		if(connection!=null){
			String sql = "insert into smbms_provider (proCode,proName,proDesc," +
					"proContact,proPhone,proAddress,proFax,createdBy,creationDate) " +
					"values(?,?,?,?,?,?,?,?,?)";
			Object[] params = {provider.getProCode(),provider.getProName(),provider.getProDesc(),
					provider.getProContact(),provider.getProPhone(),provider.getProAddress(),
					provider.getProFax(),provider.getCreatedBy(),provider.getCreationDate()};
			if(BaseDao.execute(connection, pstm, sql, params)>0){
				flag=true;
				BaseDao.closeResource(null, pstm, null);
			}
		}
		return flag;
	}

	@Override
	public List<Provider> getProviderList(Connection connection, String proName,int pageIndex,int pageSize)
			throws Exception {
		PreparedStatement pstm = null;
		ResultSet rs=null;
		List<Provider> list = new ArrayList<Provider>();
		if(connection!=null){
			String sql="select * from smbms_provider where proName like ?limit ?,?";
			Page page=new Page();
			page.setCounts(getCounts(proName, connection));
			page.setPageIndex(pageIndex);
			page.setPageSize(pageSize);
			int start =page.getStart();
			int end=page.getEnd();
			Object[] params = { "%" + proName + "%",start,end };
			rs=BaseDao.execute(connection, pstm, rs, sql, params);
		   while(rs.next()){
			   Provider p=new Provider();
			  p.setId(rs.getInt("id"));
			  p.setProCode(rs.getString("proCode"));             
			  p.setProName(rs.getString("proName"));             
			  p.setProDesc(rs.getString("proDesc"));             
			  p.setProContact(rs.getString("proContact"));       
			  p.setProPhone(rs.getString("proPhone"));           
			  p.setProAddress(rs.getString("proAddress"));       
			  p.setProFax(rs.getString("proFax"));               
			  p.setCreationDate(rs.getTimestamp("creationDate"));
			  list.add(p);
		   }
		   BaseDao.closeResource(null, pstm, rs);
		}
		return list;
	}

	@Override
	public boolean deleteProviderById(Connection connection, String delId)
			throws Exception {
		PreparedStatement pstm=null;
		boolean flag=false;
		if(connection!=null){
			String sql = "delete from smbms_provider where id=?";
            Object[] params={delId};
            if(BaseDao.execute(connection, pstm, sql, params)>0){
            	flag=true;
            	BaseDao.closeResource(null, pstm, null);
            }
		}
		return flag;
	}

	@Override
	public Provider getProviderById(Connection connection, String id)
			throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm=null;
		ResultSet rs=null;
		Provider provider=null;
		if(connection!=null){
			String sql="select * from smbms_provider where id=?";
			Object[]params={id};
			rs=BaseDao.execute(connection, pstm, rs, sql, params);
			while(rs.next()){
				provider = new Provider();
				provider.setId(rs.getInt("id"));
				provider.setProCode(rs.getString("proCode"));
				provider.setProName(rs.getString("proName"));
				provider.setProDesc(rs.getString("proDesc"));
				provider.setProContact(rs.getString("proContact"));
				provider.setProPhone(rs.getString("proPhone"));
				provider.setProAddress(rs.getString("proAddress"));
				provider.setProFax(rs.getString("proFax"));
				provider.setCreatedBy(rs.getInt("createdBy"));
				provider.setCreationDate(rs.getTimestamp("creationDate"));
				provider.setModifyBy(rs.getInt("modifyBy"));
				provider.setModifyDate(rs.getTimestamp("modifyDate"));
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return provider;
	}

	@Override
	public boolean modify(Connection connection, Provider provider)
			throws Exception {
		boolean flag= false;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		if(connection!=null){
			String sql = "update smbms_provider set proDesc=?,proContact=?," +
					"proPhone=?,proAddress=?,proFax=?,modifyBy=?,modifyDate=? where id = ? ";
			Object[] params = {provider.getProDesc(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(),
					provider.getProFax(),provider.getModifyBy(),provider.getModifyDate(),provider.getId()};
			if(BaseDao.execute(connection, pstm, sql, params)>0){
				flag=true;
				BaseDao.closeResource(null, pstm, null);
			}

		}
		return flag;
	}

	@Override
	public int getCounts(String proName, Connection connection)
			throws Exception {
		int counts = 0;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		if (connection != null) {
			String sql = "SELECT COUNT(*) FROM smbms_provider where proName like ?";
			Object[] params = {"%" + proName + "%"};
			try {
				rs = BaseDao.execute(connection, pstm, rs, sql, params);
				while (rs.next()) {
					counts = rs.getInt(1);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return counts;
	}
	
}
